--region [dbo].[AddLogReply]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[AddLogReply]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[AddLogReply]') IS NOT NULL
	DROP PROCEDURE [dbo].[AddLogReply]
GO

CREATE PROCEDURE [dbo].[AddLogReply]
	@LogTopicId int,
	@Body ntext,
	@FloorNo int,
	@ReplyFloorNo int,
	@PostUserId int,
	@PostUserName nvarchar(50),
	@Ip nvarchar(50),
	@PostTime datetime,
	@UpdateTime datetime
AS

INSERT INTO [LogReply] (
	[LogTopicId],
	[Body],
	[FloorNo],
	[ReplyFloorNo],
	[PostUserId],
	[PostUserName],
	[Ip],
	[PostTime],
	[UpdateTime]
) VALUES (
	@LogTopicId,
	@Body,
	@FloorNo,
	@ReplyFloorNo,
	@PostUserId,
	@PostUserName,
	@Ip,
	@PostTime,
	@UpdateTime
)

GO
--endregion


--region [dbo].[UpdateLogReply]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateLogReply]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateLogReply]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateLogReply]
GO

CREATE PROCEDURE [dbo].[UpdateLogReply]
	@Id int,
	@LogTopicId int,
	@Body ntext,
	@FloorNo int,
	@ReplyFloorNo int,
	@PostUserId int,
	@PostUserName nvarchar(50),
	@Ip nvarchar(50),
	@PostTime datetime,
	@UpdateTime datetime
AS

UPDATE [LogReply] SET
	[LogTopicId] = @LogTopicId,
	[Body] = @Body,
	[FloorNo] = @FloorNo,
	[ReplyFloorNo] = @ReplyFloorNo,
	[PostUserId] = @PostUserId,
	[PostUserName] = @PostUserName,
	[Ip] = @Ip,
	[PostTime] = @PostTime,
	[UpdateTime] = @UpdateTime
WHERE
	[Id] = @Id

GO
--endregion


--region [dbo].[UpdateLogReplyState]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateLogReplyState]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateLogReplyState]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateLogReplyState]
GO

CREATE PROCEDURE [dbo].[UpdateLogReplyState]
	@lid int,
	@state int
AS

UPDATE [LogReply] SET
	[IsRead] = @state
WHERE
	[LogReply].[LogTopicId] = @lid

GO
--endregion


--region [dbo].[DelLogReply]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[DelLogReply]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[DelLogReply]') IS NOT NULL
	DROP PROCEDURE [dbo].[DelLogReply]
GO

CREATE PROCEDURE [dbo].[DelLogReply]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
DELETE FROM
	[LogReply]
WHERE
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetLogReply]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetLogReply]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetLogReply]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetLogReply]
GO

CREATE PROCEDURE [dbo].[GetLogReply]
	@Id int
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[Id],
	[LogTopicId],
	[Body],
	[FloorNo],
	[ReplyFloorNo],
	[PostUserId],
	[PostUserName],
	[Ip],
	[PostTime],
	[UpdateTime]
FROM
	[LogReply]
WHERE
	[Id] = @Id

GO
--endregion


--region [dbo].[GetLogReplyList]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetLogReplyList]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetLogReplyList]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetLogReplyList]
GO

CREATE PROCEDURE [dbo].[GetLogReplyList]
	@PageSize int,
	@PageIndex int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT(Id) 
FROM [LogReply]
	'
IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
END 

EXEC sp_executesql @SQL

SET @SQL = '
SELECT TOP ' + STR(@pagesize) + '
	[Id],
	[LogTopicId],
	[Body],
	[FloorNo],
	[ReplyFloorNo],
	[PostUserId],
	[PostUserName],
	[Ip],
	[PostTime],
	[UpdateTime]
FROM
	[LogReply]
	'

IF @PageIndex > 1
BEGIN
SET @SQL = @SQL + '
	WHERE [Id] < 
	( 
	SELECT MIN([Id])  
	FROM ( 
	SELECT TOP 
		' + STR((@PageIndex-1)*@PageSize) + ' [Id] 
	FROM [LogReply] ' 

	IF @WhereCondition <> ''
	BEGIN
		SET @SQL = @SQL + ' WHERE ' + @WhereCondition
	END

	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + '
	ORDER BY
		' + @OrderByExpression + ') AS tblTmp ) '
	END
	ELSE
	BEGIN
		SET @SQL = @SQL + ') AS tblTmp ) '
	END

	IF @WhereCondition <> ''
	BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
	END
	
	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + '
	ORDER BY
		' + @OrderByExpression 
	END	
END
ELSE 
BEGIN
	IF @WhereCondition <> ''
	BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
	END
	
	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + ' 
	ORDER BY
		' + @OrderByExpression 
	END	
END

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetLattestLogReplyByUser]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetLattestLogReplyByUser]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetLattestLogReplyByUser]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetLattestLogReplyByUser]
GO

CREATE PROCEDURE [dbo].[GetLattestLogReplyByUser]
	@N int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT TOP ' + STR(@N) + '
	[LogReply].[Id],
	[LogReply].[LogTopicId],
	[LogReply].[Body],
	[LogReply].[PostUserId],
	[LogReply].[PostUserName],
	[LogReply].[PostTime]
	
FROM
	[LogTopic],[LogReply]
	'

IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
END
	
IF @OrderByExpression <> ''
BEGIN
	SET @SQL = @SQL + ' ORDER BY ' + @OrderByExpression 
END	

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetNewLogReplyByUser]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetNewLogReplyByUser]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetNewLogReplyByUser]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetNewLogReplyByUser]
GO

CREATE PROCEDURE [dbo].[GetNewLogReplyByUser]
	@PageSize int,
	@PageIndex int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

DECLARE @SQL nvarchar(3250)
SET @SQL = '
SELECT 
	COUNT([LogReply].[Id]) 
FROM [LogTopic],[LogReply]
	'
IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
END 

EXEC sp_executesql @SQL

SET @SQL = '
SELECT 
	[LogReply].[Id],
	[LogReply].[LogTopicId],
	[LogReply].[PostUserId],
	[LogReply].[PostUserName],
	[LogReply].[PostTime],
	''LogTitle'' = [LogTopic].[Title],
	ROW_NUMBER() OVER ( ORDER BY ' + @OrderByExpression + ' ) AS SerialNumber
INTO ##t
FROM
	[LogTopic],[LogReply]
WHERE
'
IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + @WhereCondition
END

EXEC sp_executesql @SQL


SELECT *
FROM  ##t
 AS tblTmp  
WHERE tblTmp.SerialNumber > STR((@PageIndex-1)*@PageSize) AND tblTmp.SerialNumber <= STR(@PageIndex*@PageSize)
 AND [PostTime] = (SELECT MAX([PostTime]) FROM ##t WHERE [PostUserName] = tblTmp.[PostUserName] AND [LogTopicId] = tblTmp.[LogTopicId])


GO



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetNewLogReplyByUserNew]
	@PageSize int,
	@PageIndex int,
	@HostUserId int
AS
DECLARE @SQL nvarchar(3250)
SET @SQL = '
SELECT 
	COUNT([LogReply].[Id]) 
FROM 
	[LogTopic],[LogReply]
WHERE	
	[LogTopic].[Id] = [LogReply].[LogTopicId] AND
	[LogTopic].[HostUserId] =' + STR(@HostUserId) + ' 
	AND [LogReply].[PostUserId] <> ' + STR(@HostUserId) + '
	AND [LogReply].[IsRead] = 0'

EXEC sp_executesql @SQL

SET @SQL = '
SELECT * 
FROM
(
SELECT 
	[LogReply].[Id],
	[LogReply].[LogTopicId],
	[LogReply].[PostUserId],
	[LogReply].[PostUserName],
	[LogReply].[PostTime],
	''LogTitle'' = [LogTopic].[Title],
	ROW_NUMBER() OVER ( ORDER BY [LogReply].[PostTime] desc ) AS SerialNumber
FROM
	[LogTopic],[LogReply]
WHERE
	[LogTopic].[Id] = [LogReply].[LogTopicId] AND
	[LogTopic].[HostUserId] =' + STR(@HostUserId) + ' 
	AND [LogReply].[PostUserId] <> ' + STR(@HostUserId) + '
	AND [LogReply].[IsRead] = 0
) AS tblTmp 

WHERE tblTmp.SerialNumber > '
	+ STR((@PageIndex-1)*@PageSize) + ' AND 
	tblTmp.SerialNumber <= '
	+ STR(@PageIndex*@PageSize)

EXEC sp_executesql @SQL

GO



SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO



